<?php
ob_start(); 
set_time_limit(0);
ini_set('memory_limit','300M');
require_once 'DataSource.php';
require('includes/top.php');

$bk_directory = '/var/www/html/partstore/hotfolder/jcc/old/';
//$directory= $_SERVER['DOCUMENT_ROOT']."/partstore/juki/".$folder."/";
$directory = '/var/www/html/partstore/hotfolder/jcc/';

$file_stack =array();

$delete =false;

$csv = new File_CSV_DataSource;


if ($handle = opendir($directory)) {
	while (false !== ($file = readdir($handle))) {
		if(($file!=".") && ($file!="..")){
			array_push($file_stack,$file);
		}       //if(($file!=".") && ($file!="..") && (strpos($file, 'temp') === FALSE))
	} //while
	closedir($handle);
} //if ($handle = opendir($directory))
foreach($file_stack as $files)
{
        $array =array();

        $csv->load($directory.$files);
	if (!$csv->isSymmetric()) {
		die('file has headers and rows with different lengths cannot connect');
	}
        $array = $csv->connect();

        $sql = "REPLACE INTO temp_products (`prodsku`,`prodname`,`prodstatus`,`prodprice`,`prodcat`,`produsainv`,`prodmessage`) values";
        $cCount = 0;
        $sizeOfCs = sizeof($array);
        
        foreach($array as $prod)
        {
                $cCount++;
                $sku =  trim($prod['SKU']);
                $price =  trim($prod['PRICE']);
                $product_name =  trim(addslashes($prod['PRODUCTNAME']));
                $status =  trim($prod['STATUS']);
                $cat =  trim($prod['CATEGORY']);
                $messages =   trim(strtoupper($prod['MESSAGES']));
                $inventory =   (int)trim($prod['INVENTORY']);
                if ($cCount > 1)
                {
                        $sql .=",";
                }
                //build bulk insert
                $sql .="('".$sku."','".$product_name."','".$status."','".$price."','".$cat."','".$inventory."','".$messages."')";
        	//displayPercentAtInterval($sizeOfCs, $cCount);
                
        }
        //execute bulk insert
        $result = mysql_query($sql);
}

//Get current categories
$sql = "SELECT catname FROM isc_categories";
$result = mysql_query($sql);

$currCats = array();
while ($row = mysql_fetch_assoc($result))
{
        $currCats[] = "'".$row['catname']."'";
}

$currCats = implode(',', $currCats);

//get categories that are missing
$sql = "SELECT DISTINCT prodcat FROM `temp_products` WHERE prodcat NOT IN (".$currCats.")";
$result = mysql_query($sql);

if($result)
{
	$sqlCat = "INSERT INTO isc_categories (catname, catlayoutfile, catvisible) values";
	$countCats = 0;
	while ($row = mysql_fetch_assoc($result))
	{
		$countCats++;
		$sqlCat .= "('".$row['prodcat']."', 'category.html', 1)";
	}
	$result = mysql_query($sqlCat);
	echo $countCats." new Categories added to system.\n";
}

//update existing products
$sql = "SELECT tp.*, c.categoryid, p.productid FROM temp_products tp LEFT JOIN isc_products p ON p.prodcode = tp.prodsku LEFT JOIN isc_categories c ON c.catname = tp.prodcat WHERE p.prodcode IS NOT NULL";

$result = mysql_query($sql);

if ($result)
{
        $countProds = 0;
        $sql = "REPLACE INTO isc_products (`productid`,`prodname`,`prodcode`,`prodstatus`,`prodprice`,`prodcalculatedprice`,`prodvisible`,`prodcatids`,`prodtype`,`proddesc`,`produsainv`,`prodmessage`) VALUES";
        while ($row = mysql_fetch_assoc($result))
        {
                if ($row['prodstatus'] != 'T')
                {
                        $countProds++;
                        $proddesc = mysql_escape_string($row['prodname']);
                        switch($row['prodstatus'])
                        {
                                case "A":
                                        $visible = '1';
                                        break;
                                case "D":
                                        $visible = '1';
                                        $proddesc .= ' (Discontinued)';
                                        break;
                                default:
                                        $visible = '0';
                                        break;
                        }
                        if ($countProds > 1)
                        {
                                $sql .=",";
                        }

                        $sql .="('".$row['productid']."','".$row['prodsku']."','".$row['prodsku']."','".$row['prodstatus']."','".$row['prodprice']."','".$row['prodprice']."','".$visible."','".$row['categoryid']."','0','".$proddesc."','".$row['produsainv']."','".$row['prodmessage']."')";
                //displayPercentAtInterval($row['prodCount'], $countProds);
                }
        }
//      echo $sql;
        $result = mysql_query($sql);
        if ($result)
        {
                echo $countProds." products updated in the system.\n";
        }
}

//now insert new products
$sql = "SELECT tp.*, c.categoryid FROM temp_products tp LEFT JOIN isc_products p ON p.prodcode = tp.prodsku LEFT JOIN isc_categories c ON c.catname = tp.prodcat WHERE p.prodcode IS NULL";
$result = mysql_query($sql);

if ($result)
{
	$countProds = 0;	
	$sql = "INSERT INTO isc_products (`prodname`,`prodcode`,`prodstatus`,`prodprice`,`prodcalculatedprice`,`prodvisible`,`prodcatids`,`prodtype`,`proddesc`,`produsainv`,`prodmessage`) VALUES";
	while ($row = mysql_fetch_assoc($result))
	{
		if ($row['prodstatus'] != 'T')
		{
			$countProds++;
			$proddesc = mysql_escape_string($row['prodname']);
			switch($row['prodstatus'])
			{
				case "A":
					$visible = '1';
					break;
				case "D":
					$visible = '1';
					$proddesc .= ' (Discontinued)';
					break;
				default:
        	                        $visible = '0';
                        	        break;
			}
			if ($countProds > 1)
                	{
                        	$sql .=",";
                	}

			$sql .="('".$row['prodsku']."','".$row['prodsku']."','".$row['prodstatus']."','".$row['prodprice']."','".$row['prodprice']."','".$visible."','".$row['categoryid']."','0','".$proddesc."','".$row['produsainv']."','".$row['prodmessage']."')";
		//displayPercentAtInterval($row['prodCount'], $countProds);
		}
	}
//	echo $sql;	
	$result = mysql_query($sql);
	if ($result)
	{
		echo $countProds." new products added to system.\n";
	}
}

/*
$sql = "SELECT p.*, IF( pc.produsainv >0, 0, 1 ) AS visibility
FROM isc_products AS p
INNER JOIN isc_products AS pc ON p.prodcode = pc.prodmessage";
$result = mysql_query($sql);

if ($result)
{
	$countProds = 0;
	$sql = "REPLACE INTO isc_products (`productid`,`prodname`,`prodcode`,`prodstatus`,`prodprice`,`prodcalculatedprice`,`prodvisible`,`prodcatids`,`prodtype`,`proddesc`,`produsainv`,`prodmessage`) VALUES";


	while ($row = mysql_fetch_assoc($result))
	{
		$countProds++;
		if ($countProds > 1)
                {
               		$sql .=",";
                }
		
		$sql .="('".$row['productid']."','".$row['prodname']."','".$row['prodcode']."','".$row['prodstatus']."','".$row['prodprice']."','".$row['prodcalculatedprice']."','".$row['visibility']."','".$row['prodcatids']."','".$row['prodtype']."','".$row['proddesc']."','".$row['produsainv']."','".mysql_escape_string($row['prodmessage'])."')";

	
	}
	try{
	$result = mysql_query($sql);
        if ($result)
        {
                echo $countProds." replacement products visibility updated in the system.\n";
        }
	}catch(Exception $e)
	{
	pint_r($e);
	}

}
*/

//rutine to update visibility based on replacement.
$sql = "UPDATE isc_products as p,temp_products as tp SET p.prodvisible= IF(tp.produsainv = 0, 1, 0 ) WHERE p.prodcode=tp.prodmessage";
$result = mysql_query($sql);

$sql = "SELECT productid, prodcode, proddesc, prodcatids FROM isc_products";
$result = mysql_query($sql);

if ($result)
{
	
	$sqlSearch = "REPLACE INTO isc_product_search (`productid`,`prodname`,`prodcode`,`proddesc`) VALUES";
//('".$prodid."','".$product_name."','".$sku."')";

	$sqlWords = "REPLACE INTO isc_product_words (`word`,`productid`) VALUES";
//('".$product_name."','".$prodid."')";

	$sqlCatAss = "REPLACE INTO isc_categoryassociations (`productid`,`categoryid`) VALUES";
//('".$prodid."','".getCatID($prod['CATEGORY'])."')";
	$pCount = 0;
	while ($row = mysql_fetch_assoc($result))
        {
		$proddesc = mysql_escape_string($row['proddesc']);
		$glue = ",";
			
		if ($pCount == 0)
		{
			$glue = "";
		}
		$sqlSearch .= $glue."('".$row['productid']."', '".$row['prodcode']."','".$row['prodcode']."', '".$proddesc."')";

		$sqlWords .= $glue."('".$proddesc."','".$row['productid']."')";
		$sqlCatAss .= $glue."('".$row['productid']."','".$row['prodcatids']."')";
		$pCount++;
	}
	
	$result = mysql_query($sqlSearch);
        if ($result)
        {
                echo $pCount." records added to product_search.\n";
        }

	$result = mysql_query($sqlWords);
        if ($result)
        {
                echo $pCount." records added to product_words.\n";
        }
	
	$result = mysql_query($sqlCatAss);
        if ($result)
        {
                echo $pCount." records added to categoryassociations.\n";
        }
}

$sql = "DELETE from temp_products";
$result = mysql_query($sql);
